package com.ejia.server;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.ejia.dao.BaseDao;
import com.ejia.pojo.fangkong;
import com.ejia.pojo.house;
import com.ejia.pojo.user;

public class FKService {
	
	public static ArrayList<fangkong> findby() {
		String sql="SELECT\n" + 
				"		f.FkId,\n" + 
				"		u.RealName,\n" + 
				"		h.address,\n" + 
				"		f.STATUS,\n" + 
				"		f.city,\n" + 
				"		f.updateTime \n" + 
				"	FROM\n" + 
				"		tb_fangkong f\n" + 
				"		JOIN tb_house h\n" + 
				"		JOIN tb_house h1\n" + 
				"		JOIN tb_user u \n" + 
				"	WHERE\n" + 
				"		f.userId = u.Id \n" + 
				"		AND h.parentId = h1.hId\n" + 
				"		AND h.hId = u.hid";
		BaseDao baseDao= new BaseDao();
		ResultSet rSet=baseDao.query(sql);
		ArrayList<fangkong> fangkongs = new ArrayList<>();
		fangkong fangkong=null;
		user user=null;
		house house=null;
		try {
			while (rSet.next()) {
				fangkong=new fangkong();
				user = new user();
				house= new house();
				fangkong.setFkid(rSet.getInt("FkId"));
				user.setRealname(rSet.getString("RealName"));
				fangkong.setUserid(user);
				house.setAddress(rSet.getString("address"));
				fangkong.setHouseid(house);
				fangkong.setStatus(rSet.getInt("STATUS"));
				fangkong.setCity(rSet.getString("city"));
				fangkong.setUpdatetime(rSet.getDate("updateTime"));
				fangkongs.add(fangkong);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return fangkongs;
	}
	
	
	public static ArrayList<fangkong> findByLike(int zt) {
		String sql="SELECT\n" + 
				"		f.FkId,\n" + 
				"		u.RealName,\n" + 
				"		h.address,\n" + 
				"		f.STATUS,\n" + 
				"		f.city,\n" + 
				"		f.updateTime \n" + 
				"	FROM\n" + 
				"		tb_fangkong f\n" + 
				"		JOIN tb_house h\n" + 
				"		JOIN tb_house h1\n" + 
				"		JOIN tb_user u \n" + 
				"	WHERE\n" + 
				"		f.userId = u.Id \n" + 
				"		AND h.parentId = h1.hId\n" + 
				"		AND h.hId = u.hid"
				+ "	AND f.status=?";
		BaseDao baseDao= new BaseDao();
		ResultSet rSet=baseDao.query(sql,zt);
		ArrayList<fangkong> fangkongs = new ArrayList<>();
		fangkong fangkong=null;
		user user=null;
		house house=null;
		try {
			while (rSet.next()) {
				fangkong=new fangkong();
				user = new user();
				house= new house();
				fangkong.setFkid(rSet.getInt("FkId"));
				user.setRealname(rSet.getString("RealName"));
				fangkong.setUserid(user);
				house.setAddress(rSet.getString("address"));
				fangkong.setHouseid(house);
				fangkong.setStatus(rSet.getInt("STATUS"));
				fangkong.setCity(rSet.getString("city"));
				fangkong.setUpdatetime(rSet.getDate("updateTime"));
				fangkongs.add(fangkong);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return fangkongs;
	}
	
	
}
